package com.corticon.dialogs.dao.hibernate;

import java.text.ParseException;
import java.util.List;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.dao.DataAccessResourceFailureException;
import org.hibernate.HibernateException;

import com.corticon.dialogs.dao.ResponseTypeDAO;
import com.corticon.dialogs.model.Locale;
import com.corticon.dialogs.model.Question;
import com.corticon.dialogs.model.QuestionAvailabilityType;
import com.corticon.dialogs.model.QuestionSet;
import com.corticon.dialogs.model.QuestionSetInNaire;
import com.corticon.dialogs.model.Questionnaire;
import com.corticon.dialogs.model.ResponseType;
import com.corticon.dialogs.util.Pager;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Set;
import java.sql.SQLException;

import javax.annotation.Resource;

@Repository("questionnaireManager")
public class QuestionnaireManager extends HibernateDaoSupport {

	private static String lastDate = "12/31/9999 23:59:59";
	@Resource
	private ResponseTypeDAO responseTypeDAO;

	public List getUserByGroupId(String GroupId) {
		List Lquestion = this.getHibernateTemplate().find("from User as a where a.userGroup.name='" + GroupId + "'");
		if (Lquestion != null && Lquestion.size() > 0 && Lquestion.get(0) != null) {
			return Lquestion;
		}
		return null;
	}

	public Locale getLocale(String code) {
		List locales = this.getHibernateTemplate().find("from Locale as a where a.code='" + code + "'");
		if (locales != null && locales.size() > 0 && locales.get(0) != null) {
			return (Locale) locales.get(0);
		}
		return null;
	}

	public List getDialogIn(String dialogId) {
		return this.getHibernateTemplate().find("from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + dialogId + " order by cast(a.sequence as integer)");
	}

	public List getDialogNotIn(String dialogId) {
		return this.getHibernateTemplate().find("from QuestionSetInNaire as a where a.questionnaire.questionnaireId<>" + dialogId);
	}

	public List<QuestionSet> searchPage(String keyWord, String userName, String groupName) {
		String keyword_sql = "";
		if (keyWord.equals("")) {
			keyword_sql = " 1=1";
		} else {
			keyword_sql = " (lower(a.refId) like '%" + keyWord.toLowerCase() + "%' or lower(a.vocabularyname) like '%" + keyWord.toLowerCase() + "%' or lower(a.name) like '%" + keyWord.toLowerCase() + "%' or lower(a.displayTitle) like '%" + keyWord.toLowerCase()
					+ "%')";
		}
		if (!userName.equals("")) {
			keyword_sql += " and a.modifiedBy.username='" + userName + "'";
		}
		if (!groupName.equals("")) {
			keyword_sql += " and a.modifiedBy.userGroup.name='" + groupName + "'";
		}
		/**
		 * criteria.add(disjunction().add(like("refId", keywords,
		 * MatchMode.ANYWHERE)).add(like( "name", keywords,
		 * MatchMode.ANYWHERE)).add(like("description", keywords,
		 * MatchMode.ANYWHERE)));
		 */
		return this.getHibernateTemplate().find("from QuestionSet as a where " + keyword_sql);
	}

	public boolean checkPage(String dialogId, String pageId) {
		List pages = this.getHibernateTemplate().find("from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + dialogId + " and a.questionSet.questionSetId=" + pageId);
		if (pages != null && pages.size() > 0 && pages.get(0) != null) {
			return true;
		}
		return false;
	}

	public void insertPage(Questionnaire dialog, QuestionSet page) {
		QuestionSetInNaire inNaire = new QuestionSetInNaire();
		inNaire.setQuestionSet(page);
		inNaire.setQuestionnaire(dialog);
		List pages = this.getHibernateTemplate().find(
				"select max(cast(a.sequence as integer)) from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + dialog.getQuestionnaireId().toString());
		int sequence = 1;
		if (pages != null && pages.size() > 0 && pages.get(0) != null) {
			sequence = Integer.valueOf(pages.get(0).toString()) + 1;
		}
		inNaire.setSequence(String.valueOf(sequence));
		this.getHibernateTemplate().save(inNaire);
	}

	public void responseQuestion(Questionnaire dialog, QuestionSet page) {
		// find page include question
		DecimalFormat format = new DecimalFormat("000");
		String epaRefQuestionnaires = dialog.getRefId() + "V" + format.format(dialog.getVersion());

		List questions = this.getHibernateTemplate().find("select a.question from QuestionInSet as a where a.questionSet.questionSetId=" + page.getQuestionSetId().toString());
		if (questions != null && questions.size() > 0 && questions.get(0) != null) {
			for (int i = 0; i < questions.size(); i++) {
				Question question = (Question) questions.get(i);
				if (question.getResponseType().getUicontrol()!=null){
					if (question.getResponseType().getUicontrol().getName().equalsIgnoreCase("Check Box")){
						continue;
					}
				}
				if (question.getEpaRefQuestionnaires() == null || question.getEpaRefQuestionnaires().equals("")) {
					question.setEpaRefQuestionnaires(epaRefQuestionnaires + ";");
				} else {
					question.setEpaRefQuestionnaires(question.getEpaRefQuestionnaires() + ";" + epaRefQuestionnaires);
				}

				this.getHibernateTemplate().update(question);
			}

		}
		// add dialog refid/version to question epaRefQuestionnaires
	}

	public void upPage(QuestionSetInNaire naire) {
		List prevpage = this.getHibernateTemplate().find(
				"from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + naire.getQuestionnaire().getQuestionnaireId().toString() + " and cast(a.sequence as integer)<"
						+ naire.getSequence() + " order by cast(a.sequence as integer) desc");
		if (prevpage != null && prevpage.size() > 0 && prevpage.get(0) != null) {
			QuestionSetInNaire preNaire = (QuestionSetInNaire) prevpage.get(0);
			String naireSequence = naire.getSequence();
			String prenaireSequence = preNaire.getSequence();
			preNaire.setSequence(naireSequence);
			naire.setSequence(prenaireSequence);
			this.getHibernateTemplate().update(naire);
			this.getHibernateTemplate().update(preNaire);
		}
	}

	public void downPage(QuestionSetInNaire naire) {
		List nextpage = this.getHibernateTemplate().find(
				"from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + naire.getQuestionnaire().getQuestionnaireId().toString() + " and cast(a.sequence as integer)>"
						+ naire.getSequence() + " order by cast(a.sequence as integer)");
		if (nextpage != null && nextpage.size() > 0 && nextpage.get(0) != null) {
			QuestionSetInNaire nextNaire = (QuestionSetInNaire) nextpage.get(0);
			String naireSequence = naire.getSequence();
			String prenaireSequence = nextNaire.getSequence();
			nextNaire.setSequence(naireSequence);
			naire.setSequence(prenaireSequence);
			this.getHibernateTemplate().update(naire);
			this.getHibernateTemplate().update(nextNaire);
		}
	}

	public List<Questionnaire> checkOverlapping(Questionnaire naire) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String returnStr = "";
		String overlappingSql = "from Questionnaire as a where a.publicationStatus.code = 'published' and a.function.code='" + naire.getFunction().getCode() + "' and a.region.code='"
				+ naire.getRegion().getCode() + "' and a.productType.code='" + naire.getProductType().getCode() + "' and a.locale.code='" + naire.getLocale().getCode() + "' and a.refId <> '"
				+ naire.getRefId() + "' and (a.effectiveDate<='" + sdf.format(naire.getExpirationDate()) + "' or a.expirationDate>='" + sdf.format(naire.getEffectiveDate()) + "')";
		List<Questionnaire> overlappingnaires = this.getHibernateTemplate().find(overlappingSql);
		return overlappingnaires;
	}

	public void publishQuestionnaire(Questionnaire naire) throws ParseException {
		SimpleDateFormat strToDate = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		// parse format String to date
		Date expirationDate = strToDate.parse(lastDate);
		naire.setExpirationDate(expirationDate);
		Calendar cal = Calendar.getInstance();
		if (naire.getVersion() <= 1) {
			// check other questionnaire
			this.getHibernateTemplate().update(naire);
		} else {
			// check same questionnaire version
			List<Questionnaire> preVersions = this.getHibernateTemplate().find(
					"from Questionnaire as a where a.publicationStatus.code = 'published' and a.refId='" + naire.getRefId() + "' and a.version<" + naire.getVersion() + " order by a.version desc");
			if (preVersions != null && preVersions.size() > 0 && preVersions.get(0) != null) {
				for (int i = 0; i < preVersions.size(); i++) {
					cal.setTime(naire.getEffectiveDate());
					cal.add(cal.DATE, -1);
					Date endDate = cal.getTime();
					if (i == 0) {
						Questionnaire preversionnaire = (Questionnaire) preVersions.get(0);
						// yestoday

						if (sdf.format(preversionnaire.getExpirationDate()).equals(sdf.format(expirationDate))) {
							preversionnaire.setExpirationDate(endDate);
							this.getHibernateTemplate().update(preversionnaire);
						}
					} else {
						Questionnaire cheeckotherversionnaire = (Questionnaire) preVersions.get(i);
						if (cheeckotherversionnaire.getExpirationDate().after(endDate)) {
							cheeckotherversionnaire.setExpirationDate(endDate);
							this.getHibernateTemplate().update(cheeckotherversionnaire);
						}
					}
				}

			}
			this.getHibernateTemplate().update(naire);
		}
		// By publishing the Dialog, its status is set to “Published”, and all
		// Pages and Questions included in this Dialog are updated with a status
		// setting of “Locked”.
		// lock all pages
		List availabilitys = this.getHibernateTemplate().find("from QuestionAvailabilityType as a where a.code='locked'");
		QuestionAvailabilityType availability = (QuestionAvailabilityType) availabilitys.get(0);
		List allPages = this.getHibernateTemplate().find("select a.questionSet from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + naire.getQuestionnaireId());
		if (allPages != null && allPages.size() > 0 && allPages.get(0) != null) {
			for (int i = 0; i < allPages.size(); i++) {
				QuestionSet set = (QuestionSet) allPages.get(i);
				List allSets = this.getHibernateTemplate().find("select a.question from QuestionInSet as a where a.questionSet.questionSetId=" + set.getQuestionSetId());
				if (allSets != null && allSets.size() > 0 && allSets.get(0) != null) {
					for (int j = 0; j < allSets.size(); j++) {
						Question question = (Question) allSets.get(j);
						question.setQuestionAvailabilityType(availability);
						this.getHibernateTemplate().update(question);
					}
				}
				set.setAvailability(availability);
				this.getHibernateTemplate().update(set);
			}
		}
		// lock all question

	}

	public void delQuestionnarie(Questionnaire naire) {
		List<QuestionSetInNaire> inNaires = this.getHibernateTemplate().find("from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + naire.getQuestionnaireId().toString());
		if (inNaires != null && inNaires.size() > 0) {
			for (int i = 0; i < inNaires.size(); i++) {
				QuestionSetInNaire inNaire = (QuestionSetInNaire) inNaires.get(i);
				this.getHibernateTemplate().delete(inNaire);
			}

		}
		List inSiblingNaires = this.getHibernateTemplate().find("from Questionnaire as a where a.sibling.questionnaireId=" + naire.getQuestionnaireId().toString());
		if (inSiblingNaires != null && inSiblingNaires.size() > 0) {
			for (int i = 0; i < inSiblingNaires.size(); i++) {
				Questionnaire inSiblingNaire = (Questionnaire) inSiblingNaires.get(i);
				inSiblingNaire.setSibling(null);
				this.getHibernateTemplate().update(inSiblingNaire);
			}

		}
		naire.setSibling(null);

		this.getHibernateTemplate().update(naire);
		this.getHibernateTemplate().delete(naire);
		// this.getHibernateTemplate().delete(naire);
	}

	public void delNarieInSet(Questionnaire naire) {
		DecimalFormat format = new DecimalFormat("000");
		String epaRefQuestionnaires = naire.getRefId() + "V" + format.format(naire.getVersion());
		// delete question eparefdialogs begin
		List allPages = this.getHibernateTemplate().find("select a.questionSet from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + naire.getQuestionnaireId());
		if (allPages != null && allPages.size() > 0 && allPages.get(0) != null) {
			for (int i = 0; i < allPages.size(); i++) {
				QuestionSet set = (QuestionSet) allPages.get(i);
				List allSets = this.getHibernateTemplate().find("select a.question from QuestionInSet as a where a.questionSet.questionSetId=" + set.getQuestionSetId());
				if (allSets != null && allSets.size() > 0 && allSets.get(0) != null) {
					for (int j = 0; j < allSets.size(); j++) {
						Question question = (Question) allSets.get(j);
						// question.setEpaRefQuestionnaires(question.getEpaRefQuestionnaires()
						// + ";" + epaRefQuestionnaires);
						if (question.getEpaRefQuestionnaires() != null) {
							String deleteRef = question.getEpaRefQuestionnaires();
							if (deleteRef.indexOf(epaRefQuestionnaires) != -1) {
								deleteRef = deleteRef.replaceAll(epaRefQuestionnaires + ";", "");
							}
							question.setEpaRefQuestionnaires(deleteRef);
							this.getHibernateTemplate().update(question);
						}

					}
				}

			}
		}
		// delete question eparefdialogs end

		List<QuestionSetInNaire> inNaires = this.getHibernateTemplate().find("from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + naire.getQuestionnaireId().toString());
		if (inNaires != null && inNaires.size() > 0) {
			for (int i = 0; i < inNaires.size(); i++) {
				QuestionSetInNaire inNaire = (QuestionSetInNaire) inNaires.get(i);
				this.getHibernateTemplate().delete(inNaire);
			}
		}

		this.getHibernateTemplate().update(naire);

	}

	public List getPortalList(String tableName) {
		return this.getHibernateTemplate().find("from " + tableName + " as a where a.userGroup.name='ALL'");
	}

	public List getGroupList(String tableName, String groupName) {
		return this.getHibernateTemplate().find("from " + tableName + " as a where a.userGroup.name='ALL' or a.userGroup.name = '" + groupName + "'");
	}
	public List<ResponseType> getResponseType(){		
		List<ResponseType> resType = this.getHibernateTemplate().find("from ResponseType as a where a.name != null");
		ResponseType r = new ResponseType();
		r.setName("Dynamic");
		ResponseType r1 = new ResponseType();
		r1.setName("Custom");
		resType.add(r);
		resType.add(r1);
		return resType;		
	}
    
	public void deletePageinQuestion(Long pageInQuestionId) {
		try {
			deleteFromResponse(pageInQuestionId);
			deleteFromDisplayFlag(pageInQuestionId);
			deleteFromDialogInstance(pageInQuestionId);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deletePage(Long pageId) {
		try {
			deleteFromResponsePage(pageId);
			deleteFromDisplayFlagPage(pageId);
			deleteFromDialogInstancePage(pageId);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deleteFromDialogInstance(Long pageInQuestionId) {
		this.getHibernateTemplate().bulkUpdate("update Dialog as a set a.lastQuestionPage.id=null where a.lastQuestionPage.id="+ pageInQuestionId);
		//this.getHibernateTemplate().deleteAll(this.getHibernateTemplate().find("from Dialog as a where a.lastQuestionPage.id=" + pageInQuestionId));
		try {
			this.getSession().flush();
			this.getSession().connection().commit();
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteFromResponse(Long pageInQuestionId) {
		this.getHibernateTemplate().deleteAll(this.getHibernateTemplate().find("from Response as a where a.question.id=" + pageInQuestionId));
		try {
			this.getSession().flush();
			this.getSession().connection().commit();
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteFromDisplayFlag(Long pageInQuestionId) {
		this.getHibernateTemplate().deleteAll(this.getHibernateTemplate().find("from DisplayFlagRecord as a where a.pageQuestion.id=" + pageInQuestionId));
		try {
			this.getSession().flush();
			this.getSession().connection().commit();
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteFromDialogInstancePage(Long pageId) {
		List Lsql = this.getHibernateTemplate().find("from Dialog as a where a.lastQuestionPage.questionSet.questionSetId="+ pageId);
		try {
			this.getSession().flush();
			this.getSession().connection().commit();
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteFromResponsePage(Long pageId) {
		this.getHibernateTemplate().deleteAll(this.getHibernateTemplate().find("from Response as a where a.question.questionSet.questionSetId=" + pageId));
		try {
			this.getSession().flush();
			this.getSession().connection().commit();
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteFromDisplayFlagPage(Long pageId) {

		List Lsql = this.getHibernateTemplate().find("from DisplayFlagRecord as a where a.pageQuestion.questionSet.questionSetId=" + pageId);
		this.getHibernateTemplate().deleteAll(Lsql);
		try {
			this.getSession().flush();
			this.getSession().connection().commit();
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public List<Question> getAllQuestions(Questionnaire dialog){
		List<Question> questions = new ArrayList();
		List allPages = this.getHibernateTemplate().find("select a.questionSet from QuestionSetInNaire as a where a.questionnaire.questionnaireId=" + dialog.getQuestionnaireId());
		if (allPages != null && allPages.size() > 0 && allPages.get(0) != null) {
			for (int i = 0; i < allPages.size(); i++) {
				QuestionSet set = (QuestionSet) allPages.get(i);
				List allSets = this.getHibernateTemplate().find("select a.question from QuestionInSet as a where a.questionSet.questionSetId=" + set.getQuestionSetId());
				if (allSets != null && allSets.size() > 0 && allSets.get(0) != null) {
					for (int j = 0; j < allSets.size(); j++) {
						Question question = (Question) allSets.get(j);
						String resType = question.getResponseType().getName()==null?"":question.getResponseType().getName();
						if (!resType.equals("Display")){
							//if (!resType.equals("No response")){
								questions.add(question);
							//}
							
						}						
					}
				}
				
			}
		}
		return questions;
	}
}